﻿using QLInternet.Connection;
using QLInternet.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Controls;
using QLInternet.Components;
using System.Collections.ObjectModel;
using MySql.Data.MySqlClient;
using QLInternet.ViewModel;
using System.Windows;

namespace QLInternet.Source
{
    class QuanLy
    {
        public ObservableCollection<SettingTypeViewModel> loadDSKieuCaiDat(Connect c)
        {
            ObservableCollection<SettingTypeViewModel> li = new ObservableCollection<SettingTypeViewModel>();
            string sql = "select makieucaidat, tenkieucaidat from kieucaidat";
            MySqlDataReader re = null;
            if (!c.IsOpenning)
                c.getConnect();
            MySqlCommand cmd = new MySqlCommand(sql, c.con);

            re = cmd.ExecuteReader();
            int i = 0;
            while (re.Read())
            {
                SettingTypeViewModel st = new SettingTypeViewModel
                {
                    STT = ++i,
                    MaKieuCD = re.GetInt16(0),
                    TenKieuCD = re.GetString(1)
                };
                li.Add(st);
            }
            re.Close();
            return li;
        }

        public ObservableCollection<Modem> loadDSModem(Connect c)
        {
            ObservableCollection<Modem> li = new ObservableCollection<Modem>();
            string sql = "select mamodem, tenmodem from modem";
            MySqlDataReader re = null;
            if (!c.IsOpenning)
                c.getConnect();
            MySqlCommand cmd = new MySqlCommand(sql, c.con);
                re = cmd.ExecuteReader();
            
            int i = 0;
            while (re.Read())
            {
                Modem st = new Modem
                {
                    STT = ++i,
                    MaModem = re.GetInt16(0),
                    TenModem = re.GetString(1)
                };
                li.Add(st);
            }
            re.Close();
            return li;
        }


        public ObservableCollection<SettingType> loadKieuCaiDat(Connect c)
        {
            ObservableCollection<SettingType> li = new ObservableCollection<SettingType>();
            string sql = "select makieucaidat, tenkieucaidat from kieucaidat";
            MySqlDataReader re = null;
            if (!c.IsOpenning)
                c.getConnect();
            MySqlCommand cmd = new MySqlCommand(sql, c.con);
            re = cmd.ExecuteReader();
            
            while (re.Read())
            {
                SettingType st = new SettingType
                {
                    
                    MaKieu = re.GetInt16(0),
                    TenKieu = re.GetString(1)
                };
                li.Add(st);
            }

            re.Close();
            return li;
        }
    


        public void insertNewKieuCaiDat(Connect c, string value)
        {
            string sql = "insert into kieucaidat(tenkieucaidat) values('" + value +"')";
            MySqlCommand cmd = new MySqlCommand(sql, c.con);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Thêm kiểu cài đặt mới thành công", "Thành công");
        }

        public bool insertNewModem(Connect c, string value)
        {
            string sql = "insert into Modem(tenmodem) values('" + value + "')";
            MySqlCommand cmd = new MySqlCommand(sql, c.con);
            if (cmd.ExecuteNonQuery() > 0)
            {
                return true;
            }
            else
                return false;
            
        }

        public bool updateModem(Connect c, int ma, string val)
        {
            bool re = false;
            string sql = "update modem set tenmodem= '" + val +
                "' where mamodem=" + ma;
            if (!c.IsOpenning)
                c.getConnect();
            MySqlCommand cmd = new MySqlCommand(sql, c.con);
            if (cmd.ExecuteNonQuery() > 0)
            {
                re = true;
            }

            return re;
        }

        public bool DeleteModem(Connect c, int ma)
        {
            bool re = false;
            string sql = "delete from modem where mamodem= " + ma;
            if (!c.IsOpenning)
                c.getConnect();
            MySqlCommand cmd = new MySqlCommand(sql, c.con);
            if (cmd.ExecuteNonQuery() > 0)
            {
                re = true;
            }

            return re;
        }


        public ObservableCollection<SettingTypeViewModel> loadDonGiaAndKieuCaiDat(Connect c)
        {
            ObservableCollection<SettingTypeViewModel> li = new ObservableCollection<SettingTypeViewModel>();
            string sql = "select tenkieucaidat, tenmodem, dongia from " +
             " kieucaidat inner join chitietkieucaidat on kieucaidat.makieucaidat = chitietkieucaidat.makieucaidat" +
             " inner join modem on chitietkieucaidat.mamodem = modem.mamodem ";
            if (!c.IsOpenning)
                c.getConnect();
            MySqlCommand cmd = new MySqlCommand(sql, c.con);
            MySqlDataReader re = null;
            try
            {
                re = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Có lỗi xảy ra trong quá trình kết nối dữ liệu. " + ex.Message, "Lỗi");
            }
            int i = 0;
            while (re.Read())
            {
                i++;
                SettingTypeViewModel t = new SettingTypeViewModel
                {
                    STT = i,
                    TenKieuCD = re.GetString(0),
                    TenModem = re.GetString(1),
                    DonGia = re.GetInt32(2)
                };
                li.Add(t);
            }



            re.Close();

            return li;
        }

        public ObservableCollection<GoiCuocViewModel> loadDSGoiCuoc(Connect c)
        {
            ObservableCollection<GoiCuocViewModel> li = new ObservableCollection<GoiCuocViewModel>();

            string sql = "select tengoicuoc, downloadtoida, uploadtoida, downloadtoithieu, uploadtoithieu, diachiip, " +
                " goicuoc.maphuongthuc, trongoi, dongia, denmb, moimbsudung, cuoctoida, magoicuoc " +
             " from goicuoc inner join phuongthucluuluong on goicuoc.maphuongthuc = phuongthucluuluong.maphuongthuc " +
             " inner join chitietloailuuluong on chitietloailuuluong.maloailuuluong = phuongthucluuluong.maphuongthuc ";
            if (!c.IsOpenning)
                c.getConnect();
            MySqlCommand cmd = new MySqlCommand(sql, c.con);
            MySqlDataReader re = null;
            try
            {
                re = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Có lỗi xảy ra trong quá trình kết nối dữ liệu. " + ex.Message, "Lỗi");
            }
            int i = 0;
            while (re.Read())
            {
                i++;

                string phuongThuc = "";
                int maPt = re.GetInt16(6);
                int donGia = re.GetInt32(8);
                int denMb = re.GetInt32(9);
                int moiMB = re.GetInt32(10);
                int toiDa = re.GetInt32(11);
                switch (maPt)
                {
                    case 1:
                        phuongThuc = "Không cung cấp";
                        break;
                    case 2:
                        phuongThuc = "Đến " + denMb + ": " + donGia + ".\n"
                            + "Mỗi MB tiếp theo: " + moiMB + ".\n"
                            + "Cước tối đa: " + toiDa;
                        break;
                    case 3:
                        phuongThuc = "Đến " + denMb + ": " + donGia + ".\n"
                            + "Mỗi MB tiếp theo: " + moiMB + ".\n"
                            + "Cước tối đa: " + toiDa;
                        break;
                    case 4:
                        phuongThuc = "Cước thuê bao: " + donGia + ".\n"
                            + "Mỗi MB tiếp theo: " + moiMB + ".\n"
                            + "Cước tối đa: " + toiDa;
                        break;
                    case 5:
                        phuongThuc = "Cước thuê bao: " + donGia + ".\n"
                            + "Mỗi MB tiếp theo: " + moiMB + ".\n"
                            + "Cước tối đa: " + toiDa;
                        break;

                }
                GoiCuocViewModel t = new GoiCuocViewModel()
                {
                    STT = i,
                    MaGoiCuoc = re.GetInt16(12),
                    TenGoiCuoc = re.GetString(0),
                    DownToiDa = re.GetInt32(1),
                    UpToiDa = re.GetInt32(2),
                    DownToiThieu = re.GetInt32(3),
                    UpToiThieu = re.GetInt32(4),
                    IP = re.GetInt16(5),
                    PhuongThuc = phuongThuc,
                    GiaTronGoi = re.GetInt32(7),
                    CuocToiDa = toiDa,
                    DenMB = denMb,
                    DonGia = donGia,
                    MoiMB = moiMB,
                    MaPhuongThuc = maPt

                };
                li.Add(t);
            }





            re.Close();

            return li;

        }

        public bool deleteGoiCuoc(int maGC, Connect c)
        {
            bool re = false;
            string sql = "delete from goicuoc where magoicuoc= " + maGC;
            if (!c.IsOpenning)
                c.getConnect();
            MySqlCommand cmd = new MySqlCommand(sql, c.con);
            if (cmd.ExecuteNonQuery() > 0)
            {
                re = true;
            }
            
            return re;
        }

        public bool insertGoiCuoc(Connect c, string tenGoi, int downMax, int downMin, int upMax, int upMin,
            int tronGoi, int soIP, int maPt)
        {
            bool re = false;
            string sql = "insert into goicuoc(tengoicuoc,downloadtoida,uploadtoida, downloadtoithieu, uploadtoithieu" + 
            ",diachiip,maphuongthuc,trongoi) values('"+ tenGoi + 
            "',"+ downMax + "," + upMax + "," + downMin + "," + upMin + "," + soIP + "," +maPt + ", " + tronGoi+ ")";
            
            MySqlCommand cmd = new MySqlCommand(sql, c.con);
            if (cmd.ExecuteNonQuery() > 0)
            {
                re = true;
            }
            return re;
                
        }
        public bool updateGoiCuoc(Connect c,int  ma, string tenGoi, int dowmMax, int downMin, int upMax, int upMin, int tronGoi, int maPT, int soIP)
        {
            bool re = false;
            string sql = "update goicuoc set tengoicuoc= '" + tenGoi + 
                "', downloadtoida= " + dowmMax + 
                ", downloadtoithieu=" + downMin+
                ", uploadtoida = " + upMax + 
                ", uploadtoithieu=" + upMin + 
                ", trongoi = " + tronGoi+
                ", maphuongthuc=" + maPT+
                ", diachiIP = " +soIP +
                " where magoicuoc=" + ma;
            if (!c.IsOpenning)
                c.getConnect();
            MySqlCommand cmd = new MySqlCommand(sql, c.con);
            if (cmd.ExecuteNonQuery() > 0)
            {
                re = true;
            }

            return re;
        }

        public ObservableCollection<PhuongThuc> loadDSPhuongThuc(Connect c)
        {
            ObservableCollection<PhuongThuc> li = new ObservableCollection<PhuongThuc>();
            string sql = "select maphuongthuc, moimbsudung, cuoctoida, denmb, dongia from phuongthucluuluong" + 
             " inner join chitietloailuuluong on phuongthucluuluong.maloailuuluong = chitietloailuuluong.maloailuuluong";
            if (!c.IsOpenning)
                c.getConnect();
            MySqlCommand cmd = new MySqlCommand(sql, c.con);
            MySqlDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {
                PhuongThuc p = new PhuongThuc { MaPT = r.GetInt16(0), MoiMB = r.GetInt32(1), CuocToiDa = r.GetInt32(2),
                 DenMB = r.GetInt32(3), DonGia = r.GetInt32(4)};
                li.Add(p);
            }
            r.Close();
            return li;
        }


        public bool insertNewChiTietCaiDat(Connect c, int maCD, int maMD, int dG)
        {
            bool re = false;
            string sql = "insert into chitietkieucaidat(makieucaidat, mamodem, dongia) values(" + maCD + ","
                + maMD + "," +dG+")";
            if (!c.IsOpenning)
                c.getConnect();
            MySqlCommand cmd = new MySqlCommand(sql, c.con);
            if (cmd.ExecuteNonQuery() > 0)
                re = true;
            return re;
        }

        public bool updateChiTietCaiDat(Connect c, int maCD, int maMD, int dG)
        {
            bool re = false;
            string sql = "update chitietkieucaidat set makieucaidat = " + maCD + 
                ", mamodem = "+ maMD + ", dongia=" + dG + 
                " where makieucaidat = "+ maCD + " and mamodem = " + maMD;
            if (!c.IsOpenning)
                c.getConnect();
            MySqlCommand cmd = new MySqlCommand(sql, c.con);
            if (cmd.ExecuteNonQuery() > 0)
                re = true;
            return re;
        }
           
    }
}
